package com.meet.dao;

import com.meet.entity.Workers;
import com.meet.util.JDBCutil;
import com.meet.vo.WorkersVo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author LiDianyang
 * @Date 2022/8/11 19:30
 * @PackageName:com.meet.dao
 * @ClassName: WorkersDaoImpl
 * @Description: TODO
 * @Version 1.0
 */
public class WorkersDaoImpl implements WorkersDao{
    @Override
    public int addWorkers(Workers workers) throws SQLException {

        Connection connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into workers (u_name,u_password,sex,phonenumber,job,d_id) values (?,?,?,?,?,?)");
        preparedStatement.setObject(1,workers.getUname());
        preparedStatement.setObject(2,workers.getUpassword());
        preparedStatement.setObject(3,workers.getSex());
        preparedStatement.setObject(4,workers.getPhonenumber());
        preparedStatement.setObject(5,workers.getJob());
        preparedStatement.setObject(6,workers.getDid());
        int line = preparedStatement.executeUpdate();
        JDBCutil.closeConnection();
        return line;
    }


    public int updateWorkers(Workers workers) throws SQLException {

        Connection connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("update workers set u_name=?,u_password=?,sex=?,phonenumber=?,job=?,d_id=? where id=?  ");
        preparedStatement.setObject(1,workers.getUname());
        preparedStatement.setObject(2,workers.getUpassword());
        preparedStatement.setObject(3,workers.getSex());
        preparedStatement.setObject(4,workers.getPhonenumber());
        preparedStatement.setObject(5,workers.getJob());
        preparedStatement.setObject(6,workers.getDid());
        preparedStatement.setObject(7,workers.getId());
        int line = preparedStatement.executeUpdate();
        JDBCutil.closeConnection();
        return line;
    }


    public int deleteWorkers(Integer id) throws SQLException {

        Connection connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("delete from workers  where id=? ");
        preparedStatement.setObject(1,id);
        int line = preparedStatement.executeUpdate();
        JDBCutil.closeConnection();
        return line;

    }

    public List<WorkersVo> getWorkersCard(int page, int number) throws SQLException {

        List<WorkersVo> list = new ArrayList<WorkersVo>();

        Connection connection = null;
        connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select w.id, u_name ,w.u_password,w.sex,w.phonenumber,w.job, d.d_name  from workers w , department d WHERE w.d_id=d.id  order by w.id limit ?,?");
        preparedStatement.setObject(1,page);
        preparedStatement.setObject(2,number);

        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            WorkersVo workers = new WorkersVo();
            workers.setId(resultSet.getInt("id"));
            workers.setuName(resultSet.getString("u_name"));
            workers.setuPassword(resultSet.getString("u_password"));
            workers.setSex(resultSet.getBoolean("sex"));
            workers.setPhonenumber(resultSet.getString("phonenumber"));
            workers.setJob(resultSet.getString("job"));
            workers.setdName(resultSet.getString("d_name"));
            list.add(workers);
        }

        resultSet.close();
        JDBCutil.closeConnection();
        return list;
    }

    @Override
    public List<Workers> getAllWorkers() throws SQLException {
        List<Workers> list = new ArrayList<Workers>();

        Connection connection = null;
        connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select *  from workers");

        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            Workers workers = new Workers();
            workers.setId(resultSet.getInt("id"));
            workers.setUname(resultSet.getString("u_name"));
            workers.setUpassword(resultSet.getString("u_password"));
            workers.setSex(resultSet.getBoolean("sex"));
            workers.setPhonenumber(resultSet.getString("phonenumber"));
            workers.setJob(resultSet.getString("job"));
            workers.setDid(resultSet.getInt("d_id"));
            list.add(workers);
        }
        resultSet.close();
        JDBCutil.closeConnection();
        return list;
    }


    public int getCount() throws SQLException {
        Connection connection = JDBCutil.getConnection();
        //发送sql语句
        PreparedStatement preparedStatement = connection.prepareStatement("select count(*) from workers");
        ResultSet resultSet = preparedStatement.executeQuery();
        int count=0;
        if(resultSet.next()){
            count=resultSet.getInt(1);
        }else{
            throw new SQLException("表中没有数据！");
        }
        return count;
    }

    @Override
    public Workers getWorkers(String name, String password) throws SQLException {
        Connection connection = JDBCutil.getConnection();
        Workers workers = new Workers();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from workers where u_name = ? and u_password=?" );
        preparedStatement.setObject(1, name);
        preparedStatement.setObject(2, password);
        ResultSet resultSet = preparedStatement.executeQuery();

        if(resultSet.next()){

            workers.setId(resultSet.getInt(1));
            workers.setUname(resultSet.getString(2));
            workers.setUpassword(resultSet.getString(3));
            workers.setSex(resultSet.getBoolean(4));
            workers.setPhonenumber(resultSet.getString(5));
            workers.setJob(resultSet.getString(6));
            workers.setDid(resultSet.getInt(7));

        }else{
            throw new SQLException("表中没有数据！");
        }


        return workers;
    }

    public Workers getWorkers(Integer id) throws SQLException {
        Connection connection = JDBCutil.getConnection();
        Workers workers = new Workers();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from workers where id=?" );
        preparedStatement.setObject(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();

        if(resultSet.next()){

            workers.setId(resultSet.getInt(1));
            workers.setUname(resultSet.getString(2));
            workers.setUpassword(resultSet.getString(3));
            workers.setSex(resultSet.getBoolean(4));
            workers.setPhonenumber(resultSet.getString(5));
            workers.setJob(resultSet.getString(6));
            workers.setDid(resultSet.getInt(7));

        }else{
            throw new SQLException("表中没有数据！");
        }


        return workers;
    }

    /**
     * 获取所有员工数据
     *
     * @return
     * @throws SQLException
     * @author King_of_hehe
     */
    @Override
    public List<Workers> getAll() throws SQLException {

        List<Workers> list = new ArrayList<>();

        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "select * from workers";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()){
            Workers worker = new Workers();

            worker.setId(resultSet.getInt(1));
            worker.setUname(resultSet.getString(2));
            worker.setUpassword(resultSet.getString(3));
            worker.setSex(resultSet.getBoolean(4));
            worker.setPhonenumber(resultSet.getString(5));
            worker.setJob(resultSet.getString(6));
            worker.setDid(resultSet.getInt(7));

            list.add(worker);

        }

        return list;
    }
    //----曾广龙
    @Override
    public WorkersVo getWorkersVo(Integer id) throws SQLException {

        WorkersVo workers = new WorkersVo();
        Connection connection = null;
        connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select w.id, u_name ,w.u_password,w.sex,w.phonenumber,w.job, d.d_name  from workers w , department d  WHERE w.d_id=d.id and w.id=?");
        preparedStatement.setObject(1,id);

        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){

            workers.setId(resultSet.getInt("id"));
            workers.setuName(resultSet.getString("u_name"));
            workers.setuPassword(resultSet.getString("u_password"));
            workers.setSex(resultSet.getBoolean("sex"));
            workers.setPhonenumber(resultSet.getString("phonenumber"));
            workers.setJob(resultSet.getString("job"));
            workers.setdName(resultSet.getString("d_name"));

        }

        resultSet.close();
        JDBCutil.closeConnection();
        return workers;
    }
    public int updateWorkersphone(Workers workers) throws SQLException {

        Connection connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("update workers set phonenumber=? where id=?  ");

        preparedStatement.setObject(1,workers.getPhonenumber());
        preparedStatement.setObject(2,workers.getId());
        int line = preparedStatement.executeUpdate();
        JDBCutil.closeConnection();
        return line;
    }


}
